﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Lion
{
    public static class DataHelper
    {
        private static IDbConnection GetConnection()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["LionConnectionString"].ConnectionString;
            IDbConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }
        private static void CreateCommandParameters(IDbCommand command, object[] parameterValues)
        {
            if (parameterValues != null)
            {
                int index = 1;
                foreach (object paramValue in parameterValues)
                {
                    IDbDataParameter param = command.CreateParameter();
                    param.ParameterName = "@P" + index;
                    if (paramValue == null)
                        param.Value = DBNull.Value;
                    else
                        param.Value = paramValue;
                    command.Parameters.Add(param);
                    index++;
                }
            }
        }
        public static IDataReader ExecuteReader(string sql, params object[] parameterValues)
        {
            var connection = GetConnection();
            IDbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            CreateCommandParameters(command, parameterValues);
            IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;

        }
        public static object ExecuteScalar(string sql, params object[] parameterValues)
        {
            object value = null;
            using (IDbConnection connection = GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    CreateCommandParameters(command, parameterValues);
                    value = command.ExecuteScalar();
                }
            }
            return value;
        }
        public static DataTable GetDataTable(string sql, params object[] parameterValues)
        {
            DataTable dataTable = new DataTable();
            using (IDbConnection connection = GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command))
                    {
                        command.CommandText = sql;
                        CreateCommandParameters(command, parameterValues);
                        da.Fill(dataTable);

                    }
                }
            }
            return dataTable;
        }
        public static DataSet GetDataSet(string sql, params object[] parameterValues)
        {
            DataSet dataSet = new DataSet();
            using (IDbConnection connection = GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command))
                    {
                        command.CommandText = sql;
                        CreateCommandParameters(command, parameterValues);
                        da.Fill(dataSet);
                    }
                }
            }
            return dataSet;
        }
        public static int ExecuteNonQuery(string sql, params object[] parameterValues)
        {
            int rowsAffected = 0;
            using (IDbConnection connection = GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandTimeout = 0;
                    command.CommandText = sql;
                    CreateCommandParameters(command, parameterValues);
                    rowsAffected = command.ExecuteNonQuery();
                }
            }
            return rowsAffected;
        }

    }
}
